<?php
class Mod_main extends CI_Model
{
    function __construct()
    {
        parent::__construct();
		$this->load->model('engine/master');
    }
	

    function get_Data_Jurusan()
    {
    	$sql	= "SELECT
					`kode_jurusan`
					, `nama_jurusan`
					FROM tbl_jurusan";
    	$rec	= $this->db->query($sql);
    	$res	= $rec->result_array();
    	return $res;
    }
    
	function get_galeri_depan()
	{
		$sql	= "SELECT
					`id_banner`
					, `name`
					, `url`
					, `image`
					FROM tbl_banner
					WHERE publish = 'y'
					AND id_banner_category = '1'
					ORDER BY id_banner DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['image_s']= $this->selipin_name($res[$i]['image'], "admin");
			$res[$i]['image']= $this->selipin_name($res[$i]['image'], "front");
			$res[$i]['judul_html']= $this->modif_title($res[$i]['name']);
		}
		return $res;
	}

	function get_banner_sekolah()
	{
		$sql	= "SELECT
					`id_banner`
					, `name`
					, `url`
					, `image`
					FROM tbl_banner
					WHERE publish = 'y'
					AND id_banner_category = '2'
					ORDER BY id_banner DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['image']= $this->selipin_name($res[$i]['image'], "resize");
			$res[$i]['judul_html']= $this->modif_title($res[$i]['name']);
		}
		return $res;
	}

	function get_galeri_full()
	{
		$sql	= "SELECT
					id_kategori_galeri
					, kategori
					, sampul
					FROM tbl_kategori_galeri
					ORDER BY id_kategori_galeri DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res as $i=>$r)
		{
			if($res[$i]['sampul']!="")
			$res[$i]['sampul']= $this->selipin_name($res[$i]['sampul'], "crop");
			$res[$i]['kategori_html']= $this->modif_title($res[$i]['kategori']);
		}
			return $res;
	}
	
	function insert_kontak_kami($nama, 
								$email,
								$subjek,
								$isi)
	{
		$output = "";
		if($nama == "")
		{
			$output = "Tolong isi nama.";
		}
		elseif($email == "")
		{
			$output = "Tolong isi email.";
		}
		elseif($subjek == "")
		{
			$output = "Tolong isi subjek";
		}
		elseif($isi == "")
		{
			$output = "Jangan kosongin kotak Isi";
		}
		else {
			$sql	= "
					INSERT INTO tblkontak 
							(`nama`, `email`, `subjek`, `isi`, `baca`, `tanggal`, `jam`)
					VALUES 
							('".$nama."', '".$email."', '".$subjek."', '".$isi."', 'n', '".date("Ymd")."', '".date("H:i:s")."') 
					";	
			$rec	= $this->db->query($sql);
			$output = "ok";
		}
		return $output;
	}

	function insert_pendaftaran(
			$nama,
			$jeniskelamin,
			$tempatlahir,
			$tanggallahir,
			$agama,
			$alamat,
			$telp,
			$email,
			$sekolahasal,
			$jenjang,
			$nem,
			$kodejurusan,
			$status_pekerjaan,
			$status_perkawinan,
			$pekerjaan_orang_tua,
			$alamat_orang_tua,
			$pilih,
			$id_periode
			)
	{
		$newid = "";
		// bikin id
		$sql = "select count(*) as jum from tbl_data_pmb";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		$jumlahrec = $res[0]["jum"]+1;
		$newid = "PMB".substr("000000".$jumlahrec,-6);
		
		$output = "";
			$sql	= "
					INSERT INTO tbl_data_pmb 
							(`id_data_pmb`,
							`nama`, 
							`jenis_kelamin`, 
							`tgl_lahir`, 
							`tempat_lahir`,
							`agama`, 
							`alamat`, 
							`telp`, 
							`email`, 
							`sekolah_asal`, 
							`jenjang`,
							`nem`, 
							`kode_jurusan`, 
							`tgl_daftar` ,
							`status_pekerjaan`,
							`status_perkawinan`,
							`pekerjaan_orang_tua`,
							`alamat_orang_tua`,
							`pilihan_pmb`,
							`id_periode`
					)
					VALUES 
							('".$newid."',
							 '".$nama."',
							 '".$jeniskelamin."', 
							 '".$tanggallahir."', 
							 '".$tempatlahir."', 
							 '".$agama."', 
							 '".$alamat."', 
							 '".$telp."', 
							 '".$email."', 
							 '".$sekolahasal."', 
							 '".$jenjang."', 
							 '".$nem."', 
							 '".$kodejurusan."', 
							 '".date("Ymd")."' ,
							 '".$status_pekerjaan."', 
							 '".$status_perkawinan."', 
							 '".$pekerjaan_orang_tua."', 
							 '".$alamat_orang_tua."' ,
							 '".$pilih."',
							 '".$id_periode."'
							)
					";	
//			echo $sql;
			$rec	= $this->db->query($sql);
			$output = "ok";
			
		return $this->get_data_pmb($newid);
	}
	
	function get_data_pmb($idmahasiswa)
	{
		$sql	= "
					SELECT
							`id_data_pmb`,
							`nama`,
							`jenis_kelamin`,
							`tgl_lahir`,
							`tempat_lahir`,
							`agama`,
							`alamat`,
							`telp`,
							`email`,
							`sekolah_asal`,
							`jenjang`,
							`nem`,
							`kode_jurusan`,
							`tgl_daftar`
					FROM tbl_data_pmb
					WHERE id_data_pmb='".$idmahasiswa."'
					";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if(count($res)>0)
			return $res[0];
		return false;
	}

	function get_album($id)
	{
		$sql	= "SELECT
					g.id_galeri
					, g.judul
					, g.keterangan
					, g.gambar
					, g.tanggal
					, kg.kategori
					FROM tbl_galeri g
					INNER JOIN tbl_kategori_galeri kg
					WHERE g.id_kategori_galeri='$id'
					GROUP BY g.id_galeri
					ORDER BY g.id_kategori_galeri DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['gambar'], "admin");
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
		}
		return $res;
	}

	function get_periode_pendaftaran()
	{
		$sql	= "SELECT
		id, periode
		FROM tbl_periode_pendaftaran
		WHERE tanggal_mulai<='".date("Y/m/d")."'
		AND tanggal_akhir>='".date("Y/m/d")."'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}
	
	function get_detail_galeri($id)
	{
		$sql	= "SELECT
					g.id_galeri
					, g.judul
					, g.keterangan
					, g.gambar
					, g.tanggal
					, kg.kategori
					FROM tbl_galeri g
					INNER JOIN tbl_kategori_galeri kg
					WHERE g.id_galeri='$id'
					GROUP BY g.id_galeri
					ORDER BY g.id_kategori_galeri DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['gambar'], "small");
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
		}
		return $res;
	}

	function get_page_full($id)
	{
		$sql	= "SELECT
					id_page
					, title
					, image
					, keterangan
					FROM tbl_page
					WHERE id_page = '$id'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['image'], "small");
		}
		if(count($res)>0)
			return $res[0];
		return "";
	}

	function get_slide_depan()
	{
		$sql	= "SELECT
					c.id_content
					, c.judul
					, c.isi
					, c.tanggal
					, i.image_url
					FROM tbl_content c
					INNER JOIN tbl_content_image i
					ON i.id_content = c.id_content
					WHERE c.id_kategori = '1'
					GROUP BY c.id_content
					ORDER BY c.id_content DESC
					LIMIT 5";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['image_url'], "crop");
			$res[$i]['isi']= substr(strip_tags($res[$i]['isi']),0,100);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
		}
		return $res;
	}

	function get_content_short($id_kategori, $limit=10, $start=0)
	{
		$sql	= "SELECT
					c.id_content
					, c.judul
					, c.isi
					, c.tanggal
					, i.image_url
					FROM tbl_content c
					LEFT JOIN tbl_content_image i
					ON i.id_content = c.id_content
					WHERE c.id_kategori = '$id_kategori'
					GROUP BY c.id_content
					ORDER BY c.id_content DESC
					LIMIT $start, $limit";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['image_url'], "crop");
			$res[$i]['isi_s']= substr(strip_tags($res[$i]['isi']),0,50);
			$res[$i]['isi']= substr(strip_tags($res[$i]['isi']),0,100);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
		}
		return $res;
	}

	function get_content_total($id_kategori)
	{
		$sql	= "SELECT
					count(id_content) AS jml
					FROM tbl_content
					WHERE id_kategori = '$id_kategori'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res[0]['jml'];
	}
	
	function get_materi_short($id_kategori, $limit=10, $start=0)
	{
		$sql	= "SELECT
					e.id_elearning
					, e.judul
					, e.isi
					, e.tanggal
					, g.gambar
					, gr.nama
					, mp.mata_kuliah
					FROM tbl_elearning e
					INNER JOIN tbl_elearning_gambar g
						ON g.id_elearning = e.id_elearning
					INNER JOIN tbl_dosen gr
						ON gr.kode_dosen = e.kode_dosen
					INNER JOIN tbl_mata_kuliah mp
						ON mp.kode_mata_kuliah = e.kode_mata_kuliah
					WHERE e.kode_mata_kuliah = '$id_kategori'
					GROUP BY e.id_elearning
					ORDER BY e.id_elearning DESC
					LIMIT $start, $limit";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['gambar'], "admin");
			$res[$i]['isi']= substr(strip_tags($res[$i]['isi']),0,100);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
		}
		return $res;
	}

	function get_materi_full($id_content)
	{
		$sql	= "SELECT
					e.id_elearning
					, e.judul
					, e.isi
					, e.tanggal
					, g.gambar
					, gr.nama
					, mp.kode_mata_kuliah
					, mp.mata_kuliah
					FROM tbl_elearning e
					INNER JOIN tbl_elearning_gambar g
						ON g.id_elearning = e.id_elearning
					INNER JOIN tbl_dosen gr
						ON gr.kode_dosen = e.kode_dosen
					INNER JOIN tbl_mata_kuliah mp
						ON mp.kode_mata_kuliah = e.kode_mata_kuliah
					WHERE e.id_elearning = '$id_content'
					GROUP BY e.id_elearning";
				$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$sql2	= "SELECT gambar
						FROM tbl_elearning_gambar
						WHERE id_elearning = '".$r['id_elearning']."'";
			$rec2	= $this->db->query($sql2);
			$res2	= $rec2->result_array();
			foreach($res2 as $i2=>$r2)
			{
				$res2[$i2]['gambar_small']= $this->selipin_name($res2[$i2]['gambar'], "admin");
				$res2[$i2]['gambar']= $this->selipin_name($res2[$i2]['gambar'], "small");
			}
			$res[$i]['gambar']= $res2;

			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
		}
		if(count($res)>0)
			return $res[0];
		return array();
	}

	function get_materi_total($id_kategori)
	{
		$sql	= "SELECT
					count(e.id_elearning) AS jml
					FROM tbl_elearning e
					INNER JOIN tbl_elearning_gambar g
						ON g.id_elearning = e.id_elearning
					WHERE e.kode_mata_kuliah = '$id_kategori'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res[0]['jml'];
	}
	
	function get_arsip_elearning()
	{
		$sql	= "SELECT
					m.kode_mata_kuliah
					, m.mata_kuliah
					, count(e.id_elearning) AS jml
					FROM tbl_mata_kuliah m
					LEFT JOIN tbl_elearning e
						ON m.kode_mata_kuliah = e.kode_mata_kuliah
					GROUP BY m.kode_mata_kuliah";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['judul_html']= $this->modif_title($res[$i]['mata_kuliah']);
		}
		return $res;
	}
	
	function get_nama_mp($mp)
	{
		$sql	= "SELECT
					mata_kuliah
					FROM tbl_mata_kuliah
					WHERE kode_mata_kuliah='$mp'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		if(count($res)>0)
			return $res[0]['mata_kuliah'];
		return "";
	}

	function get_content_full($id_content)
	{
		$sql	= "SELECT
					c.id_content
					, c.judul
					, c.isi
					, c.tanggal
					, c.id_kategori
					, c.meta_desc
					FROM tbl_content c
					WHERE c.id_content='$id_content'";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$sql2	= "SELECT image_url
						FROM tbl_content_image
						WHERE id_content = '".$r['id_content']."'";
			$rec2	= $this->db->query($sql2);
			$res2	= $rec2->result_array();
			foreach($res2 as $i2=>$r2)
			{
				$res2[$i2]['gambar_small']= $this->selipin_name($res2[$i2]['image_url'], "crop");
			}
			$res[$i]['gambar']= $res2;

			$sql3	= "SELECT keyword
									FROM tbl_content_keyword
									WHERE id_content = '".$r['id_content']."'";
			$rec3	= $this->db->query($sql3);
			$res3	= $rec3->result_array();
			$key_word = "";
			foreach($res3 as $r3)
			{
				$key_word	.= $r3['keyword'].", ";
			}
			
			$res[$i]['keyword']= $key_word;
				
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
		}
		if(count($res)>0)
			return $res[0];
		return array();
	}

	function get_menu_main()
	{
		$sql	= "SELECT
					id_page
					, title
					FROM tbl_page
					WHERE id_parent='0'
					ORDER BY id_page";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$sql2	= "SELECT
						id_page
						, title
						FROM tbl_page
						WHERE id_parent='".$res[$i]['id_page']."'
						ORDER BY id_page";
			$rec2	= $this->db->query($sql2);
			$res2	= $rec2->result_array();
			foreach($res2 as $i2=>$r2)
			{
				$res2[$i2]['title_html']= $this->modif_title($res2[$i2]['title']);
			}
			$res[$i]['sub']	= $res2;
		}
		return $res;
	}

	function get_learning_short($limit)
	{
		$sql	= "SELECT
					e.id_elearning
					, e.judul
					, e.isi
					, e.tanggal
					, g.gambar
					, gr.nama
					, mp.mata_kuliah
					FROM tbl_elearning e
					INNER JOIN tbl_elearning_gambar g
						ON g.id_elearning = e.id_elearning
					INNER JOIN tbl_dosen gr
						ON gr.kode_dosen = e.kode_dosen
					INNER JOIN tbl_mata_kuliah mp
						ON mp.kode_mata_kuliah = e.kode_mata_kuliah
					GROUP BY e.id_elearning
					ORDER BY e.id_elearning DESC
					LIMIT $limit";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['gambar'], "admin");
			$res[$i]['isi']= substr(strip_tags($res[$i]['isi']),0,100);
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
		}
		return $res;
	}

	function get_galery_short($limit)
	{
		$sql	= "SELECT
					g.id_galeri
					, g.judul
					, g.gambar
					, g.tanggal
					FROM tbl_galeri g
					WHERE g.publish = '1'
					ORDER BY g.id_galeri DESC
					LIMIT $limit";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['gambar'], "admin");
			$res[$i]['tanggal']= $this->modif_tanggal($res[$i]['tanggal']);
			$res[$i]['judul_html']= $this->modif_title($res[$i]['judul']);
		}
		return $res;
	}

	function get_support_online_sekolah()
	{
		$sql	= "SELECT
					id_support_online
					, image_file
					, `name`
					, id_ym
					, category
					FROM tbl_support_online
					ORDER BY category DESC";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['gambar']= $this->selipin_name($res[$i]['image_file'], "admin");
		}
		return $res;
	}

	function get_support_online_sekolah_kategori()
	{
		$sql	= "SELECT
					id_support_online_category
					, category
					FROM tbl_support_online_category";
		$rec	= $this->db->query($sql);
		$res	= $rec->result_array();
		return $res;
	}

	function selipin_name($image, $selipan)
	{
		if($image=="")
		return "";
		return $this->master->getNameofFileName($image)
				."-"
				. $selipan
				. "."
				.$this->master->getTypeofFileName($image);
	}
	
	function modif_tanggal($tgl, $hari_tampil = true)
	{
		$bulan	= array("Jan"
						, "Feb"
						, "Mar"
						, "Apr"
						, "Mei"
						, "Jun"
						, "Jul"
						, "Agu"
						, "Sep"
						, "Okt"
						, "Nov"
						, "Des");
		$hari	= array("Senin"
						, "Selasa"
						, "Rabu"
						, "Kamis"
						, "Jum'at"
						, "Sabtu"
						, "Minggu");
		$tahun_split	= substr($tgl, 0, 4);
		$bulan_split	= substr($tgl, 5, 2);
		$hari_split		= substr($tgl, 8, 2);
		$tmpstamp		= mktime(0, 0, 0, $bulan_split, $hari_split, $tahun_split);
		$bulan_jadi 	= $bulan[date("n", $tmpstamp)-1];
		$hari_jadi 		= $hari[date("N", $tmpstamp)-1];
		if(!$hari_tampil)
		$hari_jadi="";
		return $hari_jadi." ".$hari_split." ".$bulan_jadi." ".$tahun_split;
	}
	
	function getIdentitasSiswa($nis)
	{
		$sql = "SELECT
				nim
				, nama
				, photo
				FROM tbl_siswa
				WHERE nim='$nis'";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['photo']= $this->selipin_name($res[$i]['photo'], "admin");
		}
		return $res;
	}
	
	function getIdentitasGuru($nik)
	{
		$sql = "SELECT
						kode_dosen
						, nama
						, photo
						FROM tbl_dosen	
						WHERE kode_dosen='$nik'";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		foreach($res	as $i=>$r)
		{
			$res[$i]['photo']= $this->selipin_name($res[$i]['photo'], "admin");
		}
		return $res;
	}
	
	function get_current_callender()
	{
		$sql = "SELECT
				c.id_calender,
				c.calender_name
				FROM tbl_academic_calender c
				INNER JOIN tbl_academic_calender_day cd
					ON c.id_calender = cd.id_calender
				WHERE
				cd.tgl_mulai<='".date("Y-m-d")."'
				AND cd.tgl_selesai>='".date("Y-m-d")."'
				GROUP BY c.id_calender
				ORDER BY c.id_calender		
		";
		$rec = $this->db->query($sql);
		$res = $rec->result_array();
		for($i=0; $i<count($res); $i++)
		{
			$sql = "SELECT
							c.id_calender,
							cd.tgl_mulai,
							cd.tgl_selesai
							FROM tbl_academic_calender c
							INNER JOIN tbl_academic_calender_day cd
								ON c.id_calender = cd.id_calender
							WHERE
							cd.tgl_mulai<='".date("Y-m-d")."'
							AND cd.tgl_selesai>='".date("Y-m-d")."'
							AND c.id_calender = '".$res[$i]['id_calender']."'
							GROUP BY c.id_calender
							ORDER BY c.id_calender		
					";
			$rec2 = $this->db->query($sql);
			$res2 = $rec2->result_array();
			foreach($res2 as $u=>$o)
			{
				$res2[$u]['tgl_mulai'] = $this->modif_tanggal($res2[$u]['tgl_mulai'], false);
				$res2[$u]['tgl_selesai'] = $this->modif_tanggal($res2[$u]['tgl_selesai'], false);
			}
			$res[$i]['jadwal'] = $res2;
		}
		return $res;
	}
	
	function get_inolabs_banner()
	{
		$this->load->library('curl');  
		$json_url = 'http://shs.inolabs.net/server_banner_shs/banner.php';
		$result =  $this->curl->simple_get($json_url);
		return json_decode($result, true);
	}
	
	function get_support_online_inolabs()
	{
		$this->load->library('curl');  
		$json_url = 'http://shs.inolabs.net/server_support_online_shs/so.php';
		$result =  $this->curl->simple_get($json_url);
		return json_decode($result, true);
	}
	
	function modif_title($title)
	{
		$title 	= str_replace(array(" ", "'", ",", "[", "]", "?", "!"), "-", $title);
		return strtolower($title).".html";
	}
}